package fms.dao;

import fms.model.Grib2WdInfo;
import fms.util.DateUtil;
import fms.util.GribUtil;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.util.List;

/**
 * Created by Administrator on 2014/8/14.
 */
@Service("grib2InfoService")
public class Grib2InfoServiceImpl implements Grib2InfoService {
    private Logger logger = Logger.getLogger(Grib2InfoServiceImpl.class);
    @PersistenceContext
    private EntityManager em;
    @Autowired
    private Grib2InfoDao grib2InfoDao;


    @Override
    @Transactional
    public Grib2WdInfo save(Grib2WdInfo grib2WdInfo) {

        return grib2InfoDao.save(grib2WdInfo);
    }

    @Override
    @Transactional
    public Iterable<Grib2WdInfo> save(Iterable<Grib2WdInfo> grib2WdInfo) {

        return grib2InfoDao.save(grib2WdInfo);
    }

    @Override
    @Transactional
    public void flush() {

    }

    @Override
    @Transactional
    public Grib2WdInfo update(Grib2WdInfo grib2WdInfo) {

        return grib2InfoDao.saveAndFlush(grib2WdInfo);
    }

    @Override
    @Transactional
    public long count() {
        return grib2InfoDao.count();
    }

    @Override
    @Transactional
    public void delete(Grib2WdInfo grib2WdInfo) {
        grib2InfoDao.delete(grib2WdInfo);
    }

    @Override
    @Transactional
    public void deleteById(int id) {

        grib2InfoDao.deleteById(id);
    }

    @Override
    @Transactional
    public void deleteAll() {
        grib2InfoDao.deleteAll();
    }

//   为啥执行不了呢。。。
//    @Override
//    @Transactional
//    public boolean exists(long id) {
//        return grib2InfoDao.exists(id);
//    }

    /**
     * Function : 按起止有效时间、高度层计算数据库中记录数量
     * param startTime : 起始时间
     * param endTime : 终止时间
     * param alt : 高度层
     * return long : 记录数量
     */
    @Override
    public long countByTimeAlt(String startTime, String endTime, int alt) {
        StringBuffer str = new StringBuffer();
        str.append("select count(1) from GRIB2_WD_INFO u \n" +
                " where to_char(start_time, 'yyyy-MM-dd HH24:mi:ss') = :stime\n" +
                "   and to_char(end_time, 'yyyy-MM-dd HH24:mi:ss') = :etime\n" +
                "   and (u.ATMOS_PRESSURE = :alt)");

        Query query = em.createNativeQuery(str.toString());
        query.setParameter("alt", alt);
        query.setParameter("stime", startTime);
        query.setParameter("etime", endTime);

//        System.out.println("执行SQL: " + str);
//        System.out.println("startTime: " + startTime);
//        System.out.println("endTime: " + endTime);
//        System.out.println("alt: " + alt);

        Object res = query.getSingleResult();
        // 奇怪了，类型转换不行...
//        Integer count = (Integer)res;
        String strRes = res.toString();
        return Long.valueOf(strRes);
    }


    /**
     * Function : 求出起止有效时间、2个高度层的Grib2数据总数
     * param startTime : 起始时间
     * param endTime : 终止时间
     * param alt : 由于计算任一高度层、航路点的Grib2数据时，都需要其上下“两个”标准高度层的UVT分量
     * return long : 记录数量
     */
    @Override
    public long countByTimeAlt(String startTime, String endTime, int alt1, int alt2) {
        StringBuffer str = new StringBuffer();
        // 1、为了使用索引，提高效率，将or改成union all
        // 2、将to_char改成to_date提高效率
//        str.append("select count(1) from GRIB2_WD_INFO u \n" +
//                " where to_char(start_time, 'yyyy-MM-dd HH24:mi:ss') = :stime\n" +
//                "   and to_char(end_time, 'yyyy-MM-dd HH24:mi:ss') = :etime\n" +
//                "   and (u.ATMOS_PRESSURE = :alt1 or u.ATMOS_PRESSURE = :alt2)");

        str.append("select count(1) from GRIB2_WD_INFO u \n" +
                " where to_date(:stime, 'yyyy-MM-dd HH24:mi:ss') = start_time\n" +
                "   and to_date(:etime, 'yyyy-MM-dd HH24:mi:ss') = end_time\n" +
                "   and u.ATMOS_PRESSURE = :alt1\n" +
                "union all  " +
                "select count(1) from GRIB2_WD_INFO u \n" +
                " where to_date(:stime, 'yyyy-MM-dd HH24:mi:ss') = start_time\n" +
                "   and to_date(:etime, 'yyyy-MM-dd HH24:mi:ss') = end_time\n" +
                "   and u.ATMOS_PRESSURE = :alt2\n");

        Query query = em.createNativeQuery(str.toString());
        query.setParameter("alt1", alt1);
        query.setParameter("alt2", alt2);
        query.setParameter("stime", startTime);
        query.setParameter("etime", endTime);
//        System.out.println("执行SQL: " + str);
//        System.out.println("startTime: " + startTime);
//        System.out.println("endTime: " + endTime);
//        System.out.println("alt1: " + alt1);
//        System.out.println("alt2: " + alt2);
        List list = query.getResultList();
        // 奇怪了，类型转换不行...
//        Integer count = (Integer)res;

        return ((BigDecimal)list.get(0)).longValue() + ((BigDecimal)list.get(1)).longValue();
    }

    /**
     * Function : 按起止有效时间、高度层删除数据
     * param startTime : 起始时间
     * param endTime : 终止时间
     * param alt : 高度层
     * return long : 删除记录数量
     */
    @Override
    @Transactional
    public long deleteByTimeAlt(String startTime, String endTime, int alt) {
        StringBuffer str = new StringBuffer();
//        System.out.println("startTime:" + startTime);
//        System.out.println("endTime:" + endTime);
        str.append("delete from GRIB2_WD_INFO \n" +
                " where to_char(start_time, 'yyyy-MM-dd HH24:mi:ss') = :stime\n" +
                "   and to_char(end_time, 'yyyy-MM-dd HH24:mi:ss') = :etime\n" +
                "   and ATMOS_PRESSURE = :alt");

        Query query = em.createNativeQuery(str.toString());
        query.setParameter("alt", alt);
        query.setParameter("stime", startTime);
        query.setParameter("etime", endTime);
//        System.out.println(str);
        // 新增、删除或更改的语句，通过executeUpdate方法执行更新操作
        Object res = query.executeUpdate();
        // 奇怪了，类型转换不行...
//        Integer count = (Integer)res;
        String strRes = res.toString();
        return Long.valueOf(strRes);
    }



    /**
     * Function : 取得给定经、纬、高度的8个点的UGRD、VGRD、TMP信息
     * param lon : 指定经度的前后两个标准经度值
     * param lat : 指定纬度的前后两个标准纬度值
     * param alt : 指定高度层的上下两个标准气压高度层
     * return long : 取得给定经、纬、高度的8个点的UGRD、VGRD、TMP信息
     * 取值说明:
     *      1、获取当前UTC时间，并查询6小时内有效的Grib2数据，作为返回值；
     *      2、如果，6小时内的有效数据不存在or不全，则查询12小时的有效数据，作为返回值；
     *      3、如果，12小时内的有效数据不存在or不全，则查询18小时的有效数据，作为返回值；
     *      4、如果，18小时内的有效数据不存在or不全，则无法计算指定高度层、航路点的风信息；
     *      eg: 当前UTC时间为7点，则系统优先选择6点发布的6小时预报、在选0点发布的12小时预报、最后选前一天18点发布的18小时预报
     */
    @Override
    public List findByLonLatAlt(List lon, List lat, List alt) {
        Logger windCalcLogger = Logger.getLogger("windCalc");
        StringBuffer str = new StringBuffer();
        String hour = DateUtil.getCurUTCtime("HH");
        List list;
        List timelist= GribUtil.getTimes(Integer.valueOf(hour),6);
        str.append("select u.lon, u.lat, u.ATMOS_PRESSURE, u.wind_vel, u.wind_type\n" +
                "  from GRIB2_WD_INFO u\n" +
                " where to_char(start_time, 'yyyy-MM-dd HH24:mi:ss') = :stime1\n" +
                "   and to_char(end_time, 'yyyy-MM-dd HH24:mi:ss') = :etime1\n" +
                "   and (u.ATMOS_PRESSURE = :alt1)\n" +
                "   and (u.lon = :lon1)\n" +
                "   and (u.lat = :lat1)\n" +
                "union all\n" +
                "select u.lon, u.lat, u.ATMOS_PRESSURE, u.wind_vel, u.wind_type\n" +
                "  from GRIB2_WD_INFO u\n" +
                " where to_char(start_time, 'yyyy-MM-dd HH24:mi:ss') =:stime2\n" +
                "   and to_char(end_time, 'yyyy-MM-dd HH24:mi:ss') = :etime2\n" +
                "   and (u.ATMOS_PRESSURE = :alt2)\n" +
                "   and (u.lon = :lon2)\n" +
                "   and (u.lat = :lat2)\n" +
                "union all\n" +
                "select u.lon, u.lat, u.ATMOS_PRESSURE, u.wind_vel, u.wind_type\n" +
                "  from GRIB2_WD_INFO u\n" +
                " where to_char(start_time, 'yyyy-MM-dd HH24:mi:ss') = :stime3\n" +
                "   and to_char(end_time, 'yyyy-MM-dd HH24:mi:ss') =  :etime3\n" +
                "   and (u.ATMOS_PRESSURE = :alt3)\n" +
                "   and (u.lon = :lon3)\n" +
                "   and (u.lat = :lat3)\n" +
                "union all\n" +
                "select u.lon, u.lat, u.ATMOS_PRESSURE, u.wind_vel, u.wind_type\n" +
                "  from GRIB2_WD_INFO u\n" +
                " where to_char(start_time, 'yyyy-MM-dd HH24:mi:ss') = :stime4\n" +
                "   and to_char(end_time, 'yyyy-MM-dd HH24:mi:ss') = :etime4\n" +
                "   and (u.ATMOS_PRESSURE = :alt4)\n" +
                "   and (u.lon = :lon4)\n" +
                "   and (u.lat = :lat4)\n" +
                "union all\n" +
                "select u.lon, u.lat, u.ATMOS_PRESSURE, u.wind_vel, u.wind_type\n" +
                "  from GRIB2_WD_INFO u\n" +
                " where to_char(start_time, 'yyyy-MM-dd HH24:mi:ss') = :stime5\n" +
                "   and to_char(end_time, 'yyyy-MM-dd HH24:mi:ss') =  :etime5\n" +
                "   and (u.ATMOS_PRESSURE = :alt5)\n" +
                "   and (u.lon = :lon5)\n" +
                "   and (u.lat = :lat5)\n" +
                "union all\n" +
                "select u.lon, u.lat, u.ATMOS_PRESSURE, u.wind_vel, u.wind_type\n" +
                "  from GRIB2_WD_INFO u\n" +
                " where to_char(start_time, 'yyyy-MM-dd HH24:mi:ss') = :stime6\n" +
                "   and to_char(end_time, 'yyyy-MM-dd HH24:mi:ss') = :etime6\n" +
                "   and (u.ATMOS_PRESSURE = :alt6)\n" +
                "   and (u.lon = :lon6)\n" +
                "   and (u.lat = :lat6)\n" +
                "union all\n" +
                "select u.lon, u.lat, u.ATMOS_PRESSURE, u.wind_vel, u.wind_type\n" +
                "  from GRIB2_WD_INFO u\n" +
                " where to_char(start_time, 'yyyy-MM-dd HH24:mi:ss') = :stime7\n" +
                "   and to_char(end_time, 'yyyy-MM-dd HH24:mi:ss') = :etime7\n" +
                "   and (u.ATMOS_PRESSURE = :alt7)\n" +
                "   and (u.lon = :lon7)\n" +
                "   and (u.lat = :lat7)\n" +
                "union all\n" +
                "select u.lon, u.lat, u.ATMOS_PRESSURE, u.wind_vel, u.wind_type\n" +
                "  from GRIB2_WD_INFO u\n" +
                " where to_char(start_time, 'yyyy-MM-dd HH24:mi:ss') = :stime8\n" +
                "   and to_char(end_time, 'yyyy-MM-dd HH24:mi:ss') = :etime8\n" +
                "   and (u.ATMOS_PRESSURE = :alt8)\n" +
                "   and (u.lon = :lon8)\n" +
                "   and (u.lat = :lat8)")  ;
//        Query query = em.createNativeQuery(str.toString());
//
//        query.setParameter("lon1", lon.get(0));
//        query.setParameter("alt1", alt.get(0));
//        query.setParameter("lat1", lat.get(0));
//        query.setParameter("stime1", timelist.get(0));
//        query.setParameter("etime1", timelist.get(1));
//
//        query.setParameter("lon2", lon.get(0));
//        query.setParameter("alt2", alt.get(1));
//        query.setParameter("lat2", lat.get(0));
//        query.setParameter("stime2", timelist.get(0));
//        query.setParameter("etime2", timelist.get(1));
//
//        query.setParameter("lon3", lon.get(0));
//        query.setParameter("alt3", alt.get(0));
//        query.setParameter("lat3", lat.get(1));
//        query.setParameter("stime3", timelist.get(0));
//        query.setParameter("etime3", timelist.get(1));
//
//        query.setParameter("lon4", lon.get(0));
//        query.setParameter("alt4", alt.get(1));
//        query.setParameter("lat4", lat.get(1));
//        query.setParameter("stime4", timelist.get(0));
//        query.setParameter("etime4", timelist.get(1));
//
//        query.setParameter("lon5", lon.get(1));
//        query.setParameter("alt5", alt.get(0));
//        query.setParameter("lat5", lat.get(0));
//        query.setParameter("stime5", timelist.get(0));
//        query.setParameter("etime5", timelist.get(1));
//
//        query.setParameter("lon6", lon.get(1));
//        query.setParameter("alt6", alt.get(1));
//        query.setParameter("lat6", lat.get(0));
//        query.setParameter("stime6", timelist.get(0));
//        query.setParameter("etime6", timelist.get(1));
//
//        query.setParameter("lon7", lon.get(1));
//        query.setParameter("alt7", alt.get(0));
//        query.setParameter("lat7", lat.get(1));
//        query.setParameter("stime7", timelist.get(0));
//        query.setParameter("etime7", timelist.get(1));
//
//        query.setParameter("lon8", lon.get(1));
//        query.setParameter("alt8", alt.get(1));
//        query.setParameter("lat8", lat.get(1));
//        query.setParameter("stime8", timelist.get(0));
//        query.setParameter("etime8", timelist.get(1));
//
//        list=query.getResultList();
//        windCalcLogger.info("指定高度层alt:" + alt + "、航路点lon:" + lon + ",lat:" + lat);
//        windCalcLogger.info("使用6小时有效时长的GRID2数据，开始时间是："+timelist.get(0)+"，结束时间是："+timelist.get(1));


//        if(list==null||list.size()==24){
            timelist= GribUtil.getTimes(Integer.valueOf(hour),12);

            Query  query2 = em.createNativeQuery(str.toString());
            query2.setParameter("lon1", lon.get(0));
            query2.setParameter("alt1", alt.get(0));
            query2.setParameter("lat1", lat.get(0));
            query2.setParameter("stime1", timelist.get(0));
            query2.setParameter("etime1", timelist.get(1));

            query2.setParameter("lon2", lon.get(0));
            query2.setParameter("alt2", alt.get(1));
            query2.setParameter("lat2", lat.get(0));
            query2.setParameter("stime2", timelist.get(0));
            query2.setParameter("etime2", timelist.get(1));

            query2.setParameter("lon3", lon.get(0));
            query2.setParameter("alt3", alt.get(0));
            query2.setParameter("lat3", lat.get(1));
            query2.setParameter("stime3", timelist.get(0));
            query2.setParameter("etime3", timelist.get(1));

            query2.setParameter("lon4", lon.get(0));
            query2.setParameter("alt4", alt.get(1));
            query2.setParameter("lat4", lat.get(1));
            query2.setParameter("stime4", timelist.get(0));
            query2.setParameter("etime4", timelist.get(1));

            query2.setParameter("lon5", lon.get(1));
            query2.setParameter("alt5", alt.get(0));
            query2.setParameter("lat5", lat.get(0));
            query2.setParameter("stime5", timelist.get(0));
            query2.setParameter("etime5", timelist.get(1));

            query2.setParameter("lon6", lon.get(1));
            query2.setParameter("alt6", alt.get(1));
            query2.setParameter("lat6", lat.get(0));
            query2.setParameter("stime6", timelist.get(0));
            query2.setParameter("etime6", timelist.get(1));

            query2.setParameter("lon7", lon.get(1));
            query2.setParameter("alt7", alt.get(0));
            query2.setParameter("lat7", lat.get(1));
            query2.setParameter("stime7", timelist.get(0));
            query2.setParameter("etime7", timelist.get(1));

            query2.setParameter("lon8", lon.get(1));
            query2.setParameter("alt8", alt.get(1));
            query2.setParameter("lat8", lat.get(1));
            query2.setParameter("stime8", timelist.get(0));
            query2.setParameter("etime8", timelist.get(1));


            list=query2.getResultList();
            windCalcLogger.info("使用12小时有效时长的GRID2数据，开始时间是："+timelist.get(0)+"，结束时间是："+timelist.get(1));
//        }

//        if(list==null||list.size()==24){
//            timelist= GribUtil.getTimes(Integer.valueOf(hour),18);
//
//            Query  query3 = em.createNativeQuery(str.toString());
//            query3.setParameter("lon1", lon.get(0));
//            query3.setParameter("alt1", alt.get(0));
//            query3.setParameter("lat1", lat.get(0));
//            query3.setParameter("stime1", timelist.get(0));
//            query3.setParameter("etime1", timelist.get(1));
//
//            query3.setParameter("lon2", lon.get(0));
//            query3.setParameter("alt2", alt.get(1));
//            query3.setParameter("lat2", lat.get(0));
//            query3.setParameter("stime2", timelist.get(0));
//            query3.setParameter("etime2", timelist.get(1));
//
//            query3.setParameter("lon3", lon.get(0));
//            query3.setParameter("alt3", alt.get(0));
//            query3.setParameter("lat3", lat.get(1));
//            query3.setParameter("stime3", timelist.get(0));
//            query3.setParameter("etime3", timelist.get(1));
//
//            query3.setParameter("lon4", lon.get(0));
//            query3.setParameter("alt4", alt.get(1));
//            query3.setParameter("lat4", lat.get(1));
//            query3.setParameter("stime4", timelist.get(0));
//            query3.setParameter("etime4", timelist.get(1));
//
//
//            query3.setParameter("lon5", lon.get(1));
//            query3.setParameter("alt5", alt.get(0));
//            query3.setParameter("lat5", lat.get(0));
//            query3.setParameter("stime5", timelist.get(0));
//            query3.setParameter("etime5", timelist.get(1));
//
//            query3.setParameter("lon6", lon.get(1));
//            query3.setParameter("alt6", alt.get(1));
//            query3.setParameter("lat6", lat.get(0));
//            query3.setParameter("stime6", timelist.get(0));
//            query3.setParameter("etime6", timelist.get(1));
//
//
//            query3.setParameter("lon7", lon.get(1));
//            query3.setParameter("alt7", alt.get(0));
//            query3.setParameter("lat7", lat.get(1));
//            query3.setParameter("stime7", timelist.get(0));
//            query3.setParameter("etime7", timelist.get(1));
//
//            query3.setParameter("lon8", lon.get(1));
//            query3.setParameter("alt8", alt.get(1));
//            query3.setParameter("lat8", lat.get(1));
//            query3.setParameter("stime8", timelist.get(0));
//            query3.setParameter("etime8", timelist.get(1));
//
//            list=query3.getResultList();
//            windCalcLogger.info("使用18小时有效时长的GRID2数据，开始时间是："+timelist.get(0)+"，结束时间是："+timelist.get(1));
//        }

        return  list;
    }
}
